﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.IO;
using NPOI.HSSF.UserModel;
using IHISCETeim.BLL;
using IHISCETeim.Library;
using IHISCETeim.Model;
using IHISCETeim.DAL;

namespace IHISCETeim.FXTool
{
    public partial class FXSJYSYQ : Form
    {
        public FXSJYSYQ()
        {
            InitializeComponent();
        }

        OUserInfo objNews = null;
        private LoginState IsAuthenticated(string userName, string pwd)
        {
            try
            {
                objNews = CBO.FillObject<OUserInfo>(OUserDAL.UserLogin(userName, StringHelper.GetPwdEncrypt(pwd), ""));
                // UserId = objNews.UserId;
                if (objNews == null)
                {
                    return LoginState.inputerror;
                }
                else if (objNews.State == 0)
                {
                    return LoginState.other;
                }
                else
                {
                    return LoginState.success;
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                // UserId = 0;
                return LoginState.other;
            }
            finally
            {

            }
        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //InitializeWorkbook(@"xls\Book1.xls");
                //ConvertToDataTable();

                //gvData.DataSource = dataSet1.Tables[0];
                string username = this.txtUserName.Text;
                string pwd = this.txtPwd.Text;

                LoginState ls = IsAuthenticated(username, pwd);
                if (ls != LoginState.success)
                {
                    MessageBox.Show("登陆失败，请确认你的用户密码");
                    return;
                }

                this.txtImportResult.Text = "";

                SLogController.清空三井原始逾期表();
                this.txtImportResult.Text += "已清除之前数据记录" + "\r\n"; ;

                DataTable importdt = this.dataSet1.Tables[0];     
                string x = SLogController.导入三井原始逾期表(importdt);
                if (!string.IsNullOrEmpty(x))
                        this.txtImportResult.Text += x + "\r\n";
               
                MessageBox.Show("已完成导入");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "出错提示");
            }

        }

        #region 打开Execl
        HSSFWorkbook hssfworkbook;

        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);


            }
        }

        void ConvertToDataTable()
        {
            HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            //for (int j = 0; j < 5; j++)
            //{
            //    dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
            //}
            //rows.MoveNext();
            int titleRowNum = sheet.FirstRowNum;
            HSSFRow titlerow = (HSSFRow)sheet.GetRow(titleRowNum);
            for (int j = 0; j < titlerow.LastCellNum; j++)
            {
                if (titlerow.Cells[j].ToString() == "销售日期")
                {
                    dt.Columns.Add(titlerow.Cells[j].ToString(), typeof(DateTime));
                }
                else
                {
                    dt.Columns.Add(titlerow.Cells[j].ToString());
                }
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                try
                {
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (string.IsNullOrEmpty(row.GetCell(0).StringCellValue))
                    {
                        continue;
                    }

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        HSSFCell cell = row.GetCell(i) as HSSFCell;

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                catch(Exception exc) {
                    //MessageBox.Show(dr[i]);
                    MessageBox.Show(dt.Rows.Count.ToString());
                    throw new Exception(exc.Message);
                }
              
            }
            dataSet1.Tables.Add(dt);
        }
        #endregion

        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            try
            {
                string path;

                //OpenFileDialog of = new OpenFileDialog();

                // of.Filter = "Excel文件(*.xls)|*.xls";
                if (this.openFileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                {
                    return;
                }

                path = openFileDialog1.FileName;

                this.txtFilePath.Text = path;
                ////

                InitializeWorkbook(path);
                ConvertToDataTable();

                gvData.DataSource = dataSet1.Tables[0];
                // this.btnBegin.IsEnabled = true;

                this.txtMsg.Text = string.Format("本次需要导入[{0}]条,请核对需导入的数据与Excel是否一致.", dataSet1.Tables[0].Rows.Count);
            }
            catch(Exception ex) {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
